#usr/bin/env python
#conding:utf-8
#view调用，计算单个设备的统计报表
# 1、运行统计：在线时长，活跃时长
# 2、报警统计：报警次数，报警时间，报警地点
# 3、健康统计：心率，血压，运动，静止，睡眠
# 吕康宁，2020.09.02
from sqlalchemy import desc
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base	
import datetime
import json
import math
import time
import redis
import numpy as np
import pandas as pd
import requests
from data_handle.change_lnglat import *
from math import pi,sin,cos
from sys import getsizeof

#导入数据库映射，表示用sqlachemy操作django的model创建的数据库
#连接django在使用的postgresql数据库
Base = automap_base()
engine = create_engine('sqlite:///tag.db')	
Base.prepare(engine, reflect=True)	
# global session
session = Session(engine)
#建立数据库映射
Device=Base.classes.car_app_device
Multi_Media=Base.classes.car_app_multi_media
Alarm=Base.classes.car_app_alarm
Command_Log=Base.classes.car_app_command_log
WIFI_LBS_ADDR=Base.classes.car_app_wifi_lbs_addr
Daily_Report=Base.classes.car_app_daily_report

# 表格字段
# device_id
# analysis_time
# dev_active：5000s,
# dev_online:3000s，
# alarm_time：{1:times,2:times,3:times...24:times}
# alarm_kind:{alarm_kind1:times...alarm_kind9:times}
# alarm_location:{alarm_location1:times...location9:times}
# heart_rate:average:90
# blood_pres:{h_average:90,l_average:90}
# step_count:9999
# gps_mileage:7000m,
# calorie_comsume:体重乘以公里乘以1.036
# stop_time:{time：99999秒，counts:12}
# sleep:{time：99999秒，counts:12}
# sensor_analysis:
# attch_analysis:{dev_active_counts:11,dev_online_counts:11,...sleep_counts:88}
# remark
# create_time

def get_distance(point1,point2):
	# print('get_distance',point1,point2)
	lat1=point1[1]
	lat2=point2[1]
	lng1=point1[0]
	lng2=point2[0]

	EARTH_REDIUS = 6378.137

	def rad(d):
		return d * pi / 180.0

	radLat1 = rad(lat1)
	radLat2 = rad(lat2)
	lat_a = radLat1 - radLat2
	lng_b = rad(lng1) - rad(lng2)
	result_s = 2 * math.asin(math.sqrt(math.pow(sin(lat_a/2), 2) + cos(radLat1) * cos(radLat2) * math.pow(sin(lng_b/2), 2)))
	result_s = result_s * EARTH_REDIUS

	return result_s


# 通用函数
def get_lbs_address(lbs_data,device_id,only_from_db='no'):
	lbs_data=eval(lbs_data)
	all_lbs=[]
	for x in lbs_data:
		lbs=x['MCC']+','+x['MNC']+','+x['sid']+','+x['nid']+','+str(x['rssi'])
		all_lbs.append(lbs)
	bts_lbs=all_lbs[0]
	all_lbs='|'.join(all_lbs)
	lbs_lng='0'
	lbs_lat='0'
	zh_address='0'
	lbs_accuracy='0'
	mcc=lbs_data[0]['MCC']
	mnc=lbs_data[0]['MNC']
	lbs_nid=lbs_data[0]['nid']
	lbs_sid=lbs_data[0]['sid']
	lbs_mac=lbs_sid+lbs_nid
	lbs_address=session.query(WIFI_LBS_ADDR).filter(WIFI_LBS_ADDR.mac==lbs_mac).first()
	# print('all_lbs=======>',all_lbs)
	if lbs_address==None and only_from_db=='no':
		print(only_from_db)
		# get_lbs='http://api.cellocation.com:81/cell/?mcc='+mcc+'&mnc='+mnc+'&lac='+lbs_sid+'&ci='+lbs_nid+'&output=json'
		get_lbs='http://apilocate.amap.com/position?accesstype=0&imei='+device_id+'&cdma=0&bts='+bts_lbs+'&'+all_lbs+'&key=0ff8329afe2ab4e379f6af1f555c86b7'
		lbs_loca=requests.get(get_lbs)
		lbs_loca=eval(lbs_loca.text)
		# print('lbs_loca====>',lbs_loca)
		if lbs_loca.get('info')=='OK' and lbs_loca.get('status')=='1':
			response_result=lbs_loca['result']
			if response_result.get('location')!=None:
				lnglat=response_result['location'].split(',')
				# 获取的是高德地图的的接口，得到的数据为gcj02的经纬度，此处转为原始的wgs84
				change_l=gcj02_to_wgs84(float(lnglat[0]),float(lnglat[1]))
				lbs_lng=change_l['lng']
				lbs_lat=change_l['lat']
				lbs_accuracy=response_result['radius']
				zh_address=response_result['desc']

				session.add(WIFI_LBS_ADDR(mac=lbs_mac,ssid='0',sid=lbs_sid,nid=lbs_nid,mcc=mcc,mnc=mnc,rssi=lbs_data[0]['rssi'],lng=lbs_lng,lat=lbs_lat,kind='lbs',
												location=str(response_result),date_added=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
				session.commit()

	elif lbs_address!=None:

		lbs_lng=lbs_address.lng
		lbs_lat=lbs_address.lat
		zh_address=lbs_address.location

	return lbs_lng,lbs_lat,zh_address

def get_wifi_address(wifi_data,device_id,only_from_db='no'):
	# print(wifi_data)
	wifi_data=eval(wifi_data)
	all_mac=[]
	repeat_mac=[]
	# 去掉重复
	for x in wifi_data:
		if x['mac'] not in repeat_mac:
			mac=x['mac']+','+str(x['rssi'])
			all_mac.append(mac)
			repeat_mac.append(x['mac'])
	# 高德的wifi接口，必须2个wifi mac以上才有地址
	if len(all_mac)==1:
		all_mac='0'
	else:
		all_mac='|'.join(all_mac)
	f_wifi_mac=wifi_data[0]['mac']
	wifi_ssid=wifi_data[0]['ssid']
	wifi_rssi=wifi_data[0]['rssi']
	wifi_lng='0'
	wifi_lat='0'
	zh_address='0'
	wifi_accuracy='0'
	wifi_address=session.query(WIFI_LBS_ADDR).filter(WIFI_LBS_ADDR.mac==f_wifi_mac).first()
	def get_address(save_kind):
		print(only_from_db)
		get_url='http://apilocate.amap.com/position?accesstype=1&imei='+device_id+'&macs='+all_mac+'&key=0ff8329afe2ab4e379f6af1f555c86b7'
		# print(get_url)
		# print(wifi_data)
		wifi_loca=requests.get(get_url)
		wifi_loca=eval(wifi_loca.text)
		# print('wifi_loca====>',wifi_loca)
		if wifi_loca.get('info')=='OK' and wifi_loca.get('status')=='1':
			response_result=wifi_loca['result']
			if response_result.get('location')!=None:
				lnglat=response_result['location'].split(',')
				# 获取的是高德地图的wifi转lnglat的接口，得到的数据为gcj02的经纬度，此处转为原始的wgs84
				change_l=gcj02_to_wgs84(float(lnglat[0]),float(lnglat[1]))
				wifi_lng=change_l['lng']
				wifi_lat=change_l['lat']
				wifi_accuracy=response_result['radius']
				zh_address=response_result['desc']
				if save_kind=='save':
					session.add(WIFI_LBS_ADDR(mac=f_wifi_mac,ssid=wifi_ssid,sid='0',nid='0',mcc='0',mnc='0',rssi=wifi_rssi,lng=wifi_lng,lat=wifi_lat,kind='wifi',
												location=str(response_result),date_added=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
				elif save_kind=='update':
					session.query(WIFI_LBS_ADDR).filter(WIFI_LBS_ADDR.mac==f_wifi_mac).update(dict(ssid=wifi_ssid,sid='0',nid='0',mcc='0',mnc='0',rssi=wifi_rssi,lng=wifi_lng,lat=wifi_lat,kind='wifi',
																	location=str(response_result),date_added=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
					# session.update(WIFI_LBS_ADDR(mac=f_wifi_mac,))
				session.commit()
			else:
				if save_kind=='save':
					session.add(WIFI_LBS_ADDR(mac=f_wifi_mac,ssid=wifi_ssid,sid='0',nid='0',mcc='0',mnc='0',rssi=wifi_rssi,lng='0',lat='0',kind='wifi',
													location=str(wifi_data),date_added=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
					session.commit()

	# wifi地址没有存库的情况下，通过接口获取的数据，分为有经纬度和无经纬度两种，有经纬度的在location存储具体的返回信息，没有经纬度的存储原来的wifi数据
	if wifi_address==None and only_from_db=='no' and all_mac!='0' :
		get_address('save')
		
	# wifi地址有存库，但是存储的经纬度为零，并且新请求的wifi数据与原先存储的wifi数据不一样的是，可以再次进行wifi转地址的接口请求
	elif wifi_address!=None :
		if str(wifi_data)!=wifi_address.location and wifi_address.lng=='0':
			get_address('update')
		else:
			wifi_lng=wifi_address.lng
			wifi_lat=wifi_address.lat
			zh_address=wifi_address.location

	return wifi_lng,wifi_lat,zh_address



# 提前处理
def pre_handle(data):
	if data.shape[0]>1:
		# 按照时间排序，重置index索引
		data=data.sort_values('serv_receive')
		data=data.reset_index(drop=True)
		# 把设备上传的时间，转换为时间戳，即，秒
		data.loc[:,'timestamp'] = data['serv_receive'].apply(lambda x:time.mktime(time.strptime(x,'%Y-%m-%d %H:%M:%S')))

	return data

	
# 运行统计-在线时长
def online_statistic(data,s_time,e_time):
	if data.shape[0]<2:
		return '0'
	
	# 计算两个速度点之间的时间差，秒，有offline点的情况，前行减后行，从顺序来说，第1-第2，
	data.loc[:,'serv_receive_sub']=data['timestamp']-data['timestamp'].shift(1)
	data.loc[:,'online']=data['serv_receive_sub'].map(lambda x:x if x<600 else 0)
	online=data['online'].sum()

	# print('online=====>',online)
	return online

def active_statistic(data,s_time,e_time):
	if data.shape[0]<2:
		return '0'
	cacu_res={}
	chart_data={}
	active_time=0
	stop_time=0
	chart_data['active_xAxis']=[]
	chart_data['active_yAxis']=[]
	chart_data['stop_time_xAxis']=[]
	chart_data['stop_time_yAxis']=[]
	data.loc[:,'serv_receive_sub']=data['timestamp']-data['timestamp'].shift(1)
	data['speed'] = pd.to_numeric(data['speed'])
	data.loc[:,'speed_sub']=data['speed']-data['speed'].shift(1)
	active=data.loc[data['speed']!=0].copy()
	if active.shape[0]>0:
		# 计算时间差
		# active.loc[:,'step_count_time_sub']=active['timestamp']-active['timestamp'].shift(1)
		active_time=active['serv_receive_sub'].sum()
		active=active.fillna('0')
		chart_data['active_xAxis']=active['serv_receive'].tolist()
		chart_data['active_yAxis']=active['speed_sub'].tolist()

		# 步数差为零，视为静止时间,还应该加上第一条步数为零的数据数据和第一条步数不为零的数据之间的时间差，以及最后一条无步数和最后一条有步数的时间差
		stop=data.loc[data['speed_sub']==data['speed']].copy()
		if stop.shape[0]>0:
			# 计算时间差
			# stop.loc[:,'step_count_time_sub']=stop['timestamp']-stop['timestamp'].shift(1)
			# 第一个停留和最后一条停留

			stop_time=stop['serv_receive_sub'].sum()
			stop=stop.fillna('0')
			chart_data['stop_time_xAxis']=stop['serv_receive'].tolist()
			chart_data['stop_time_yAxis']=stop['serv_receive_sub'].tolist()

	cacu_res['stop_time']=stop_time
	cacu_res['active_time']=active_time
	cacu_res['chart_data']=chart_data
		# print(cacu_res)
	# print('stop_time,active_time=====>',stop_time,active_time)
	return cacu_res

def alarm_statistic(data):
	if data.shape[0]<1:
		return '0'
	# 报警类型
	data=data.sort_values('s_time')
	data=data.reset_index(drop=True)
	alarm_kind=data.groupby('kind')['kind'].count().to_dict()
	# 时段获取
	data.loc[:,'alarm_time']=data['s_time'].map(lambda x:x[11:13])
	alarm_time=data.groupby('alarm_time')['alarm_time'].count().to_dict()
	# 地点
	def handle_alarm_location(loca,device_id):
		loca=eval(loca)
		ala_loca=['0','0']
		if isinstance(loca,dict)==True:
			if loca.get('satelite')!=None:
				ala_loca=loca.get('satelite')
			elif loca.get('wifi')!=None:
				ala_loca=get_wifi_address(loca.get('wifi'),device_id)
			elif loca.get('lbs')!=None:
				ala_loca=get_wifi_address(loca.get('lbs'),device_id)

		# print('ala_loca===========>',ala_loca)
		alarm_lng=ala_loca[0]
		alarm_lat=ala_loca[1]
		alarm_lng=round(float(alarm_lng),5)
		alarm_lat=round(float(alarm_lat),5)
		return str([alarm_lng,alarm_lat])

	data.loc[:,'alarm_location']=data.apply(lambda x:handle_alarm_location(x['location'],x['device_id']),axis=1)
	alarm_location=data.groupby('alarm_location')['alarm_location'].count().to_dict()
	# print({'alarm_kind':alarm_kind,'alarm_time':alarm_time,'alarm_location':alarm_location})
	return {'alarm_kind':alarm_kind,'alarm_time':alarm_time,'alarm_location':alarm_location}

def health_statistic(data,s_time,e_time):
	if data.shape[0]<2:
		return '0'
	# 运动步数统计和停留统计
	cacu_res={}
	chart_data={}
	# 把字符串转为数字,之前转过，先注释掉，看是否会有问题
	data.loc[:,'step_count'] = pd.to_numeric(data['step_count'])
	first_step_count=data['step_count'].iloc[0]
	# 过滤值为0的数据,用新的变量来获取
	step_count=data.loc[data['step_count']!=0].copy()
	total_stepcount=0
	chart_data['step_count_xAxis']=[]
	chart_data['step_count_yAxis']=[]
	if step_count.shape[0]>1:
		# 当天的步数，最后一条不为零的步数-当天首条步数（无论是否步数为零）
		total_stepcount=step_count['step_count'].iloc[-1]-first_step_count
		# 时间点作为X轴，对于的数值作为Y轴，转为list传到前端给e-chart填充数据。
		step_count=step_count.fillna('0')
		chart_data['step_count_xAxis']=step_count['serv_receive'].tolist()
		chart_data['step_count_yAxis']=step_count['step_count'].tolist()

	cacu_res['step_count']=total_stepcount
	# print('step_count====>',total_stepcount)

	# 统计GPS里程
	mileage=data.copy()
	distance=0
	chart_data['mileage_xAxis']=[]
	chart_data['mileage_yAxis']=[]
	def get_address_from_wifilbs(trail):

		res={'lng':'0','lat':'0'}

		if trail['lng']!='0' and trail['lat']!='0':
			res={'lng':trail['lng'],'lat':trail['lat']}

		elif trail['wifi']!='0':
			result=get_wifi_address(trail['wifi'],trail['device_id'])
			if result[0]!='0' and result[1]!='0':
				res={'lng':result[0],'lat':result[1]}


		elif trail['lbs']!='0':
			result=get_lbs_address(trail['lbs'],trail['device_id'])
			if result[0]!='0' and result[1]!='0':
				res={'lng':result[0],'lat':result[1]}

		if res.get('lng')=='0' or res.get('lng')==0:
			res='0'

		return res

	mileage.loc[:,'trail']=mileage.apply(lambda x:get_address_from_wifilbs(x),axis=1)
	mileage=mileage.loc[mileage['trail']!='0']
	if mileage.empty==False:
		mileage['trail']=mileage['trail'].map(lambda x:str(x))
		mileage.loc[:,'point']=mileage['trail']+'&'+mileage['trail'].shift(1)
		mileage=mileage.fillna('0')

		def handle_point(pt):
			# print(pt)
			pt=pt.split('&')
			pt0=[float(eval(pt[0])['lng']),float(eval(pt[0])['lat'])]
			pt1=[float(eval(pt[1])['lng']),float(eval(pt[1])['lat'])]
			pt_res=[pt0,pt1]
			if pt0[0]-pt1[0]==0 and pt0[1]-pt1[1]==0:
				pt_res='0'
			return pt_res

		mileage.loc[:,'point']=mileage['point'].map(lambda x:handle_point(x) if x!='0' else x )
		mileage.loc[:,'distance']=mileage['point'].map(lambda x:get_distance(x[0],x[1]) if x!='0' else 0)
		distance=mileage['distance'].sum()
		distance=round(distance,3)
		chart_data['mileage_xAxis']=mileage['serv_receive'].tolist()
		chart_data['mileage_yAxis']=mileage['distance'].tolist()
		# print('distance====>',distance)

	cacu_res['mileage']=distance

	# 血压统计
	# 过滤值为0的数据
	blood_data=data.loc[data['blood_pres']!='0'].copy()
	h_pres_aver=0
	l_pres_aver=0
	chart_data['h_pres_xAxis']=[]
	chart_data['h_pres_yAxis']=[]
	chart_data['l_pres_xAxis']=[]
	chart_data['l_pres_yAxis']=[]
	if blood_data.shape[0]>0:
		blood_data.loc[:,'h_pres']=blood_data['blood_pres'].map(lambda x:int(eval(x)['h_pres']))
		blood_data.loc[:,'l_pres']=blood_data['blood_pres'].map(lambda x:int(eval(x)['l_pres']))
		h_pres_aver=round(blood_data['h_pres'].mean(),1)
		l_pres_aver=round(blood_data['l_pres'].mean(),1)
		blood_data=blood_data.fillna('0')
		chart_data['h_pres_xAxis']=blood_data['serv_receive'].tolist()
		chart_data['h_pres_yAxis']=blood_data['h_pres'].tolist()
		chart_data['l_pres_xAxis']=blood_data['serv_receive'].tolist()
		chart_data['l_pres_yAxis']=blood_data['l_pres'].tolist()

	cacu_res['blood_pres']={'h_pres':h_pres_aver,'l_pres':l_pres_aver}
	# print('blood_pres====>',cacu_res['blood_pres'])

	# 全天平均心率 
	data['heart_rate'] = pd.to_numeric(data['heart_rate'])
	# 过滤值为0的数据
	heart_data=data.loc[data['heart_rate']!=0].copy()
	heart_rate_aver=0
	chart_data['heart_rate_xAxis']=[]
	chart_data['heart_rate_yAxis']=[]
	if heart_data.shape[0]>0:
		heart_rate_aver=round(heart_data['heart_rate'].mean(),1)
		heart_data=heart_data.fillna('0')
		chart_data['heart_rate_xAxis']=heart_data['serv_receive'].tolist()
		chart_data['heart_rate_yAxis']=heart_data['heart_rate'].tolist()

	cacu_res['heart_rate']=heart_rate_aver
	cacu_res['chart_data']=chart_data

	# 白天心率，早8点到晚8点
	cacu_s_time=time.mktime(time.strptime(s_time,'%Y-%m-%d %H:%M:%S'))+8*3600
	cacu_e_time=time.mktime(time.strptime(e_time,'%Y-%m-%d %H:%M:%S'))-4*3600
	day_heart=data.loc[(data['timestamp']>cacu_s_time) & (data['timestamp']<cacu_e_time)].copy()

	day_heart=day_heart.loc[data['heart_rate']!=0]
	day_heart_aver=0
	chart_data['day_heart_xAxis']=[]
	chart_data['day_heart_yAxis']=[]
	if day_heart.shape[0]>0:
		day_heart_aver=round(day_heart['heart_rate'].mean(),1)
		day_heart=day_heart.fillna('0')
		chart_data['day_heart_xAxis']=day_heart['serv_receive'].tolist()
		chart_data['day_heart_yAxis']=day_heart['heart_rate'].tolist()

	cacu_res['day_heart']=day_heart_aver
	
	# print('day_heart_aver====>',day_heart_aver)

	# 白天血压，早8点到晚8点
	day_blood=data.loc[(data['timestamp']>cacu_s_time) & (data['timestamp']<cacu_e_time)].copy()
	day_blood=day_blood.loc[day_blood['blood_pres']!='0']
	day_h_pres_aver=0
	day_l_pres_aver=0
	chart_data['day_h_pres_xAxis']=[]
	chart_data['day_h_pres_yAxis']=[]
	chart_data['day_l_pres_xAxis']=[]
	chart_data['day_l_pres_yAxis']=[]
	if day_blood.shape[0]>0:
		day_blood.loc[:,'day_h_pres']=day_blood['blood_pres'].map(lambda x:int(eval(x)['h_pres']))
		day_blood.loc[:,'day_l_pres']=day_blood['blood_pres'].map(lambda x:int(eval(x)['l_pres']))
		day_h_pres_aver=round(day_blood['day_h_pres'].mean(),1)
		day_l_pres_aver=round(day_blood['day_l_pres'].mean(),1)
		day_blood=day_blood.fillna('0')
		chart_data['day_h_pres_xAxis']=day_blood['serv_receive'].tolist()
		chart_data['day_h_pres_yAxis']=day_blood['day_h_pres'].tolist()
		chart_data['day_l_pres_xAxis']=day_blood['serv_receive'].tolist()
		chart_data['day_l_pres_yAxis']=day_blood['day_l_pres'].tolist()

	cacu_res['day_blood']={'day_h_pres':day_h_pres_aver,'day_l_pres':day_l_pres_aver}
	cacu_res['chart_data']=chart_data
	# print('day_blood====>',cacu_res['day_blood'])

	# print(cacu_res)
	# print('heart_rate====>',cacu_res['heart_rate'])
	return cacu_res
	
def sleep_time_cacu(data):
	if data.shape[0]<2:
		return '0'
	all_chart_data={}
	# 晚间心率统计晚8到早8
	health_res={}
	health_echart={}
	night_heart=data.loc[data['heart_rate']!='0'].copy()
	night_heart['heart_rate']=pd.to_numeric(night_heart['heart_rate'])
	night_heart=night_heart.loc[night_heart['heart_rate']!=0]
	night_heart_aver=0
	health_echart['night_heart_xAxis']=[]
	health_echart['night_heart_yAxis']=[]
	if night_heart.shape[0]>0:
		night_heart_aver=round(night_heart['heart_rate'].mean(),1)
		night_heart=night_heart.fillna('0')
		health_echart['night_heart_xAxis']=night_heart['serv_receive'].tolist()
		health_echart['night_heart_yAxis']=night_heart['heart_rate'].tolist()

	health_res['night_heart']=night_heart_aver
	# health_res['chart_data']=health_echart
	# all_chart_data.update(health_echart)
	# print('night_heart_aver====>',night_heart_aver)


	# 晚间血压统计晚8到早8

	night_blood=data.loc[data['blood_pres']!='0'].copy()
	night_h_pres_aver=0
	night_l_pres_aver=0
	health_echart['night_h_pres_xAxis']=[]
	health_echart['night_h_pres_yAxis']=[]
	health_echart['night_l_pres_xAxis']=[]
	health_echart['night_l_pres_yAxis']=[]
	if night_blood.shape[0]>0:
		night_blood.loc[:,'night_h_pres']=night_blood['blood_pres'].map(lambda x:int(eval(x)['h_pres']))
		night_blood.loc[:,'night_l_pres']=night_blood['blood_pres'].map(lambda x:int(eval(x)['l_pres']))
		night_h_pres_aver=round(night_blood['night_h_pres'].mean(),1)
		night_l_pres_aver=round(night_blood['night_l_pres'].mean(),1)
		night_blood=night_blood.fillna('0')
		health_echart['night_h_pres_xAxis']=night_blood['serv_receive'].tolist()
		health_echart['night_h_pres_yAxis']=night_blood['night_h_pres'].tolist()
		health_echart['night_l_pres_xAxis']=night_blood['serv_receive'].tolist()
		health_echart['night_l_pres_yAxis']=night_blood['night_l_pres'].tolist()

	health_res['night_blood']={'night_h_pres':night_h_pres_aver,'night_l_pres':night_l_pres_aver}
	all_chart_data.update(health_echart)
	# print('night_blood====>',health_res['night_blood'])


	# 睡眠：统计晚8到早8，跨越一天，运动步数在10以内，心率70以下，血压108和81以内，的时间段视为睡眠（至少10分钟采集一次,暂时不限制）,参数：身高，体重，年龄，性别，疾病等
	sleep_data={}

	def cacu_sleep(cacu_1,param,p_max,p_min):
		cacu_res={}
		
		if cacu_1.shape[0]>1:
			sp_time=0
			wake_up_time=0
			light_sp_time=0
			chart_data={}
			chart_data['wake_up_xAxis']=[]
			chart_data['wake_up_yAxis']=[]
			chart_data['light_sleep_time_xAxis']=[]
			chart_data['light_sleep_time_yAxis']=[]
			chart_data['sleep_time_xAxis']=[]
			chart_data['sleep_time_yAxis']=[]
			cacu_1.loc[:,'sleep_time_sub']=cacu_1['timestamp']-cacu_1['timestamp'].shift(1)
			cacu_1.loc[:,param] = pd.to_numeric(cacu_1[param])
			# 根据参数进行过滤
			wake_up=cacu_1.loc[cacu_1[param]>p_max].copy()
			light_sleep=cacu_1.loc[(cacu_1[param]<p_max) & (cacu_1[param]>p_min)].copy()
			sleep=cacu_1.loc[cacu_1[param]<p_min].copy()
			# 计算每个有效点之间的时间差

			# 如果是依据步数或者翻转计算睡眠，需要计算错位差
			if param=='step_count' or param=='tumble':
				cacu_1.loc[:,'count_sub'] = cacu_1[param]-cacu_1[param].shift(1)
				wake_up=cacu_1.loc[cacu_1['count_sub']>p_max].copy()
				light_sleep=cacu_1.loc[(cacu_1['count_sub']<p_max) & (cacu_1['count_sub']>p_min)].copy()
				sleep=cacu_1.loc[cacu_1['count_sub']<p_min].copy()

			if wake_up.shape[0]>0:
				# 计算时间差
				# wake_up.loc[:,'sleep_time_sub']=wake_up['timestamp']-wake_up['timestamp'].shift(1)
				wake_up_time=wake_up['sleep_time_sub'].sum()
				wake_up=wake_up.fillna('0')
				chart_data['wake_up_xAxis']=wake_up['serv_receive'].tolist()
				chart_data['wake_up_yAxis']=wake_up['sleep_time_sub'].tolist()
		
			
			if light_sleep.shape[0]>0:
				# 计算时间差
				# light_sleep.loc[:,'sleep_time_sub']=light_sleep['timestamp']-light_sleep['timestamp'].shift(1)
				light_sp_time=light_sleep['sleep_time_sub'].sum()
				light_sleep=light_sleep.fillna('0')
				chart_data['light_sleep_time_xAxis']=light_sleep['serv_receive'].tolist()
				chart_data['light_sleep_time_yAxis']=light_sleep['sleep_time_sub'].tolist()

			# 步数差小于10，视为睡眠时间
			
			if sleep.shape[0]>0:
				# 计算时间差
				# sleep.loc[:,'sleep_time_sub']=sleep['timestamp']-sleep['timestamp'].shift(1)
				sp_time=sleep['sleep_time_sub'].sum()
				sleep=sleep.fillna('0')
				chart_data['sleep_time_xAxis']=sleep['serv_receive'].tolist()
				chart_data['sleep_time_yAxis']=sleep['sleep_time_sub'].tolist()

			cacu_res['sleep_time']=sp_time
			cacu_res['wake_up']=wake_up_time
			cacu_res['light_sleep_time']=light_sp_time
			cacu_res['chart_data']=chart_data
			all_chart_data[param+'cacu_sleep_chart']=chart_data

		if len(cacu_res)==0:
			cacu_res='0'
		return cacu_res


	# 以运动步数为依据的睡眠计算
	step_count=data.loc[data['step_count']!='0'].copy()
	step_cacu_sleep=cacu_sleep(step_count,'step_count',50,10)
	sleep_data['step_cacu_sleep']=step_cacu_sleep
	# print('step_cacu_sleep',step_cacu_sleep)

	# 以心率为依据的睡眠计算，公共参数90,70，理想的心率数据参数，应该来自于佩戴人不断累积的心率数据
	heart_rate=data.loc[data['heart_rate']!='0'].copy()
	heart_cacu_sleep=cacu_sleep(heart_rate,'heart_rate',90,70)
	sleep_data['heart_cacu_sleep']=heart_cacu_sleep
	# print('heart_cacu_sleep====>',heart_cacu_sleep)


	# 以血压为依据的睡眠计算,舒张也和收缩压分别计算,# 舒张压75-85，收缩压115-125
	cacu_3=data.loc[data['blood_pres']!='0'].copy()
	l_pres_cacu_sleep='0'
	h_pres_cacu_sleep='0'
	if cacu_3.shape[0]>1:
		cacu_3.loc[:,'h_pres']=cacu_3['blood_pres'].map(lambda x:int(eval(x)['h_pres']))
		cacu_3.loc[:,'l_pres']=cacu_3['blood_pres'].map(lambda x:int(eval(x)['l_pres']))
		l_pres=cacu_3.loc[cacu_3['l_pres']!=0]
		h_pres=cacu_3.loc[cacu_3['h_pres']!=0]
		l_pres_cacu_sleep=cacu_sleep(l_pres,'l_pres',85,75)
		h_pres_cacu_sleep=cacu_sleep(h_pres,'h_pres',125,115)

	sleep_data['l_pres_cacu_sleep']=l_pres_cacu_sleep
	sleep_data['h_pres_cacu_sleep']=h_pres_cacu_sleep
	# print('l_pres_cacu_sleep====>',l_pres_cacu_sleep)
	# print('h_pres_cacu_sleep====>',h_pres_cacu_sleep)

	# 以设备翻转次数为依据的睡眠计算
	tumble=data.loc[data['othermesg']!='0'].copy()
	tumble.loc[:,'tumble']=tumble['othermesg'].map(lambda x:int(eval(x)['tumble']) if 'tumble' in eval(x) else 0)
	tumble=tumble.loc[tumble['tumble']!=0]
	tumble_cacu_sleep=cacu_sleep(tumble,'tumble',20,10)

	sleep_data['tumble_cacu_sleep']=tumble_cacu_sleep
	# print('tumble_cacu_sleep====>',tumble_cacu_sleep)
	# 睡眠数据的参数比重，总共10分，心率8，血压1（高低各0.5），步数0.5，设备翻转0.5
	denominator=10
	sleep_model={'heart_cacu_sleep':8,'tumble_cacu_sleep':0.5,'l_pres_cacu_sleep':0.5,'h_pres_cacu_sleep':0.5,'step_cacu_sleep':0.5}
	total_sleep=0
	total_wake_up=0
	total_light_sleep=0
	this_chart=[]
	for x in sleep_data:
		if sleep_data[x]=='0':
			denominator=denominator-sleep_model[x]
	for y in sleep_data:
		if sleep_data[y]!='0':
			total_sleep+=sleep_data[y]['sleep_time']*sleep_model[y]/denominator
			total_wake_up+=sleep_data[y]['wake_up']*sleep_model[y]/denominator
			total_light_sleep+=sleep_data[y]['light_sleep_time']*sleep_model[y]/denominator
	# 先用手表翻转的点位来填充echart图，试试看效果
	this_chart={}
	this_chart['wake_up_xAxis']=[]
	this_chart['wake_up_yAxis']=[]
	this_chart['light_sleep_time_xAxis']=[]
	this_chart['light_sleep_time_yAxis']=[]
	this_chart['sleep_time_xAxis']=[]
	this_chart['sleep_time_yAxis']=[]
	for x in sleep_data:
		if sleep_data[x]!='0':
			this_chart=sleep_data[x]['chart_data']
			break

	sleep_res={'sleep_time':round(total_sleep),'wake_up':round(total_wake_up),'light_sleep_time':round(total_light_sleep),'health_res':health_res,'chart_data':this_chart}
	# print('sleep_data====>',sleep_res)
	return sleep_res
	


def package_save_obj(data,s_time,all_data):
	# pandas计算出来的数字结果是int64，需要用int转，否则报错
	def handle_data(a,b,p):
		if b[p]!='0' and b[p]!=0:
			if a[p]!=0:
				a[p]=round((a[p]+int(b[p]))/2,1)
			else:
				a[p]=int(b[p])
		return a


	attch_analysis={}
	chart_data={}
	day_heart='0'
	night_heart='0'
	day_blood='0'
	night_blood='0'
	step_count='0'

	health=data['health']
	if health=='0':
		health={'heart_rate':'0','blood_pres':'0','step_count':'0','mileage':'0','day_heart':'0','day_blood':'0','chart_data':'0'}

	all_data=handle_data(all_data,health,'heart_rate')

	health['l_pres']=0
	health['h_pres']=0
	if health['blood_pres']!='0':
		health['l_pres']=health['blood_pres']['l_pres']
		health['h_pres']=health['blood_pres']['h_pres']
	all_data=handle_data(all_data,health,'l_pres')
	all_data=handle_data(all_data,health,'h_pres')
	all_data=handle_data(all_data,health,'step_count')

	h_chart=health.pop('chart_data')
	if h_chart!='0':
		all_data['heart_rate_xAxis']=all_data['heart_rate_xAxis']+h_chart['heart_rate_xAxis']
		all_data['heart_rate_yAxis']=all_data['heart_rate_yAxis']+h_chart['heart_rate_yAxis']
		all_data['h_pres_xAxis']=all_data['h_pres_xAxis']+h_chart['h_pres_xAxis']
		all_data['h_pres_yAxis']=all_data['h_pres_yAxis']+h_chart['h_pres_yAxis']
		all_data['l_pres_xAxis']=all_data['l_pres_xAxis']+h_chart['l_pres_xAxis']
		all_data['l_pres_yAxis']=all_data['l_pres_yAxis']+h_chart['l_pres_yAxis']
		all_data['step_count_xAxis']=all_data['step_count_xAxis']+h_chart['step_count_xAxis']
		all_data['step_count_yAxis']=all_data['step_count_yAxis']+h_chart['step_count_yAxis']
	

	active=data['active']
	if active=='0':
		active={'active_time':'0','stop_time':'0','chart_data':'0'}

	all_data=handle_data(all_data,active,'stop_time')
	all_data=handle_data(all_data,active,'active_time')
	all_data=handle_data(all_data,data,'online')
	# all_data['online']=data['online']
	a_chart=active.pop('chart_data')

	if a_chart!='0':
		all_data['stop_time_xAxis']=all_data['stop_time_xAxis']+a_chart['stop_time_xAxis']
		all_data['stop_time_yAxis']=all_data['stop_time_yAxis']+a_chart['stop_time_yAxis']


	day_heart=health['day_heart']
	day_blood=health['day_blood']
	step_count=health['step_count']

	sleep_data=data['sleep_time']
	if sleep_data=='0':
		sleep_data={'sleep_time':'0','wake_up':'0','light_sleep_time':'0','night_heart':'0','night_blood':'0','chart_data':'0','health_res':{'night_heart':'0','night_blood':'0'}}

	health_res=sleep_data.pop('health_res')
	s_chart=sleep_data.pop('chart_data')
	night_heart=health_res['night_heart']
	night_blood=health_res['night_blood']
	all_data=handle_data(all_data,sleep_data,'sleep_time')

	if s_chart!='0':
		all_data['wake_up_xAxis']=all_data['wake_up_xAxis']+s_chart['wake_up_xAxis']
		all_data['wake_up_yAxis']=all_data['wake_up_yAxis']+s_chart['wake_up_yAxis']
		all_data['light_sleep_time_xAxis']=all_data['light_sleep_time_xAxis']+s_chart['light_sleep_time_xAxis']
		all_data['light_sleep_time_yAxis']=all_data['light_sleep_time_yAxis']+s_chart['light_sleep_time_yAxis']
		all_data['sleep_time_xAxis']=all_data['sleep_time_xAxis']+s_chart['sleep_time_xAxis']
		all_data['sleep_time_yAxis']=all_data['sleep_time_yAxis']+s_chart['sleep_time_yAxis']

	# print(chart_data,getsizeof(chart_data))
	# 健康数据综合打分，睡眠3.5，心率3，血压3，运动0.5
	# 白天心率，70-100，夜间80以内
	total_points={}
	def cacu_score(param,score,p_min,p_max,p_muti):
		total_score=0
		if param!='0' and param!=0:
			if param>p_min and param<p_max:
				min_sub=param-p_min
				max_sub=p_max-param
				if max_sub>min_sub:
					total_score=(max_sub-min_sub)*p_muti+score
				else:
					total_score=score
			else:
				if param>p_max:
					total_score=score-(param-p_max)*p_muti
				elif param<p_min:
					total_score=score-(p_min-param)*p_muti
		return total_score
	# print('health====>',day_heart,night_heart)
	day_heart_pts=cacu_score(day_heart,15,70,100,0.08)
	night_heart_pts=cacu_score(night_heart,15,60,80,0.08)
	total_points['day_heart_pts']=day_heart_pts
	total_points['night_heart_pts']=night_heart_pts
	total_points['day_blood']=0
	# print('heart_score====>',day_heart_pts,night_heart_pts)
	if day_blood!='0':
		day_h_pres=day_blood['day_h_pres']
		day_l_pres=day_blood['day_l_pres']
		day_h_pres_pts=cacu_score(day_h_pres,7.5,100,130,0.04)
		day_l_pres_pts=cacu_score(day_h_pres,7.5,70,100,0.04)
		# print('day_blood_score====>',day_h_pres_pts,day_l_pres_pts)
		total_points['day_blood']=day_h_pres_pts+day_l_pres_pts

	total_points['night_blood']=0
	if night_blood!='0':
		night_h_pres=night_blood['night_h_pres']
		night_l_pres=night_blood['night_l_pres']
		night_h_pres_pts=cacu_score(night_h_pres,7.5,90,120,0.04)
		night_l_pres_pts=cacu_score(night_h_pres,7.5,60,90,0.04)
		# print('night_blood_score====>',night_h_pres_pts,night_l_pres_pts)
		total_points['night_blood']=night_h_pres_pts+night_l_pres_pts

	total_points['step_count_pts']=0
	if step_count!=0 and step_count!='0':
		step_count_pts=step_count*0.0005
		if step_count_pts>5:
			step_count_pts=5
		# print('step_count_pts====>',step_count_pts)
		total_points['step_count_pts']=step_count_pts
	total_points['sleep_time_pts']=0
	if sleep_data['sleep_time']!='0':
		# print(sleep_data)
		def sleep_score(param,score,p_min,p_max,p_muti):
			total_score=0
			if param!='0' and param!=0:
				if param>p_min and param<p_max:
					min_sub=param-p_min
					max_sub=p_max-param
					if max_sub<min_sub:
						total_score=(min_sub-max_sub)*p_muti+score
					else:
						total_score=score
				else:
					if param>p_max:
						total_score=score
					elif param<p_min:
						total_score=score-(p_min-param)*p_muti
			return total_score
		sleep_time_pts=sleep_score(sleep_data['sleep_time']+sleep_data['light_sleep_time']*0.5,20,6*3600,8*3600,15/7200)
		# print('sleep_time_pts====>',sleep_time['sleep_time'],sleep_time['light_sleep_time'],sleep_time_pts)
		total_points['sleep_time_pts']=sleep_time_pts
	denominator=100
	health_points=0
	health_model={'sleep_time_pts':35,'day_heart_pts':15,'night_heart_pts':15,'day_blood':15,'night_blood':15,'step_count_pts':5}
	for x in total_points:
		if total_points[x]==0:
			denominator=denominator-health_model[x]
	for x in total_points:
		if total_points[x]!=0:
			health_points+=total_points[x]/(denominator/100)
	# print('total_points====>',total_points)
	# print('health_points====>',health_points)
	attch_analysis['single_points']=total_points
	attch_analysis['health_points']=round(health_points,1)
	all_data['single_points']=total_points
	all_data['health_points']=round(health_points,1)


def start_cacu(device_id,df_s_time='df',df_e_time='df'):
	# 获取当日统计时间，当天0点到24点
	time_list=[]
	if df_s_time!='df' and df_e_time!='df':
		# df_e_time=df_e_time.split(' ')
		# df_e_time[1]='00:00:00'
		# df_e_time=' '.join(df_e_time)
		df_s_time=df_s_time.split(' ')
		df_s_time[1]='00:00:00'
		df_s_time=' '.join(df_s_time)
		df_s_time=time.mktime(time.strptime(df_s_time,'%Y-%m-%d %H:%M:%S'))
		df_e_time=time.mktime(time.strptime(df_e_time,'%Y-%m-%d %H:%M:%S'))
		time_sub=df_e_time-df_s_time
		days=round(time_sub/86400)
		if days>1:
			for y in range(days):
				st=datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(df_s_time)), "%Y-%m-%d %H:%M:%S")
				et=datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(df_s_time+86399)), "%Y-%m-%d %H:%M:%S")
				time_list.append([str(st),str(et)])
				df_s_time+=86400
		else:
			st=datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(df_s_time)), "%Y-%m-%d %H:%M:%S")
			et=datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(df_s_time+86399)), "%Y-%m-%d %H:%M:%S")
			time_list.append([str(st),str(et)])
	else:
		# 服务器的默认时区与中国时区相差16小时？？有待观察
		now = time.time()
		midnight = now - (now % 86400) + time.timezone
		# print(datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(now)), "%Y-%m-%d %H:%M:%S"))
		pre_midnight = midnight - 86400
		now_midnight = midnight - 1
		s_time = datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(pre_midnight)), "%Y-%m-%d %H:%M:%S")
		e_time = datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(now_midnight)), "%Y-%m-%d %H:%M:%S")
		s_time=str(s_time)
		e_time=str(e_time)
		
		time_list.append([s_time,e_time])
	analysis_time=str(time_list[0][0])+'-'+str(time_list[0][1])
	if len(time_list)>1:
		analysis_time=str(time_list[0][0])+'-'+str(time_list[-1][1])

	all_data=dict(
	heart_rate=0,
	heart_rate_xAxis=[],
	heart_rate_yAxis=[],
	l_pres=0,
	h_pres=0,
	h_pres_xAxis=[],
	h_pres_yAxis=[],
	l_pres_xAxis=[],
	l_pres_yAxis=[],
	step_count=0,
	step_count_xAxis=[],
	step_count_yAxis=[],
	stop_time=0,
	active_time=0,
	online=0,
	stop_time_xAxis=[],
	stop_time_yAxis=[],
	sleep_time=0,
	wake_up_xAxis=[],
	wake_up_yAxis=[],
	light_sleep_time_xAxis=[],
	light_sleep_time_yAxis=[],
	sleep_time_xAxis=[],
	sleep_time_yAxis=[],
	analysis_time=analysis_time
	)
	# print(time_list)
	for t_data in time_list:
		print(t_data)
		s_time=t_data[0]
		e_time=t_data[1]
		sleep_e_time=time.mktime(time.strptime(e_time,'%Y-%m-%d %H:%M:%S'))+8*3600+1
		sleep_s_time=time.mktime(time.strptime(s_time,'%Y-%m-%d %H:%M:%S'))+20*3600
		sleep_s_time=str(datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(sleep_s_time)), "%Y-%m-%d %H:%M:%S"))
		sleep_e_time=str(datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(sleep_e_time)), "%Y-%m-%d %H:%M:%S"))
		# print(s_time,e_time,sleep_s_time,sleep_e_time)

		# 获取所有的设备的详情
		sql_device = 'select * from car_app_device where device_id=\''+device_id+'\''
		device = pd.read_sql_query(sql_device,engine)
		device.loc[:,'history_data']=device['device_id'].map(lambda x:pd.read_sql_query('select * from "'+x+'" where serv_receive between \''+s_time+'\' and \''+e_time+'\'',engine))
		device.loc[:,'sleep_cacu']=device['device_id'].map(lambda x:pd.read_sql_query('select * from "'+x+'" where serv_receive between \''+sleep_s_time+'\' and \''+sleep_e_time+'\'',engine))
		# 过滤空数据

		if device.shape[0]>0:
			device.loc[:,'history_data']=device['history_data'].map(lambda x:pre_handle(x))
			# print(device['history_data'].to_dict())
			# 计算数据1，运行，健康
			# print(device['history_data'])
			device.loc[:,'online']=device['history_data'].map(lambda x:online_statistic(x,s_time,e_time))
			device.loc[:,'active']=device['history_data'].map(lambda x:active_statistic(x,s_time,e_time))
			device.loc[:,'health']=device['history_data'].map(lambda x:health_statistic(x,s_time,e_time))
			# 计算数据2,睡眠数据，晚上8点到第二天早上8点
			device.loc[:,'sleep_cacu']=device['sleep_cacu'].map(lambda x:pre_handle(x))
			device.loc[:,'sleep_time']=device['sleep_cacu'].map(lambda x:sleep_time_cacu(x))
			device=device.fillna('0')
			device.apply(lambda x:package_save_obj(x,s_time,all_data),axis=1)


	return all_data



data_list=[]
# start_cacu('2020-09-01 00:00:00','2020-09-15 08:00:00')
# res=start_cacu('350182825342289','2020-09-11 00:00:00','2020-09-15 08:00:00')
# print(res)


# print('Daily_Report_excute_success')
# ddd=session.query(Daily_Report).filter(Daily_Report.analysis_time=='2020-09-07 00:00:00').all()
# for x in ddd:
# 	print('=====>',x.report_name,vars(x))

